package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;



public class DAOCampos {
	private Connection conn;

	public DAOCampos(){
		
	}
	
	public void finalize(){
        ConnectionManager.getInstance().checkIn(conn);
    }
	
	public List<String> getCamposTutor(int idTutor){
		List<String> campos = new ArrayList<String>();
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT c.descripcion FROM tutorcampo tc, campos c WHERE tc.idTutor = ? AND tc.idCampo = c.idCampo";
		ResultSet result = null;
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, idTutor);
			result = stmt.executeQuery();
			
			while (result.next()) {
				String s = result.getString("descripcion");
				campos.add(s);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		
		return campos;
	}
	
	public Map<Integer,String> listarCampos(){
		Map<Integer,String> lista = new HashMap<Integer,String>();
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM campos";
        ResultSet result = null;
		try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			while (result.next()) {
				String s = result.getString("descripcion");
				int id = result.getInt("idCampo");
				lista.put(id, s);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		
		return lista;
	}

	public Map<Integer, String> getCamposTutorMap(int idTutor) {
		Map<Integer,String> campos = new HashMap<Integer,String>();
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT c.descripcion, c.idCampo FROM tutorcampo tc, campos c WHERE tc.idTutor = ? AND tc.idCampo = c.idCampo";
		ResultSet result = null;
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, idTutor);
			result = stmt.executeQuery();
			
			while (result.next()) {
				int id = result.getInt("idCampo");
				String s = result.getString("descripcion");
				campos.put(id,s);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		
		return campos;
	}
	public boolean asignarCampoTutor(int idCampo, int idTutor) {
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "INSERT INTO tutorcampo (idCampo, idTutor) VALUES (?,?)";

		try{
		stmt = conn.prepareStatement(sql);
		stmt.setInt(1, idCampo);
		stmt.setInt(2, idTutor);
		stmt.execute();	
		}catch(SQLException e){
			System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
            return false;
		}finally {
        	finalize();
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		
		return true;
	}
	
	public int crearCampo(String nuevoCampo) {
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "INSERT INTO campos (idCampo, descripcion) VALUES (NULL,?)";
		int iCampo = -1;
		try{
		stmt = conn.prepareStatement(sql);
		stmt.setString(1, nuevoCampo);
		stmt.execute();	
			try{
				String sql2 = "SELECT MAX(idCampo) FROM campos";
				stmt = conn.prepareStatement(sql2);
				ResultSet rs = stmt.executeQuery();
				if(rs.next()){		
					iCampo = rs.getInt(1);
					
				}
			}catch(SQLException e){
				System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	    
			}finally {
	        	finalize();
	            try {
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }
		}catch(SQLException e){
			System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
       
		}finally {
        	finalize();
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return iCampo;

	}

	public boolean eliminarCampo(int idTutor, int idCampo) {
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "DELETE FROM tutorcampo WHERE idTutor=? AND idCampo=?";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, idTutor);
			stmt.setInt(2, idCampo);
			stmt.executeUpdate();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return true;		
	}

}
